package org.example.lethear.repository;

import org.example.lethear.entity.Report;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 举报数据访问接口
 */
@Repository
public interface ReportRepository extends JpaRepository<Report, Long> {
    
    /**
     * 根据举报者ID查找举报记录
     */
    List<Report> findByReporterId(Long reporterId);
    
    /**
     * 根据举报者ID分页查找举报记录
     */
    Page<Report> findByReporterId(Long reporterId, Pageable pageable);
    
    /**
     * 根据目标类型和目标ID查找举报记录
     */
    List<Report> findByTargetTypeAndTargetId(Integer targetType, Long targetId);
    
    /**
     * 根据状态查找举报记录
     */
    Page<Report> findByStatus(Integer status, Pageable pageable);
    
    /**
     * 根据目标类型查找举报记录
     */
    Page<Report> findByTargetType(Integer targetType, Pageable pageable);
    
    /**
     * 根据举报原因类型查找举报记录
     */
    Page<Report> findByReasonType(Integer reasonType, Pageable pageable);
    
    /**
     * 检查用户是否已举报过指定目标
     */
    boolean existsByReporterIdAndTargetTypeAndTargetId(Long reporterId, Integer targetType, Long targetId);
    
    /**
     * 查找指定目标的所有举报记录
     */
    @Query("SELECT r FROM Report r WHERE r.targetType = :targetType AND r.targetId = :targetId ORDER BY r.createTime DESC")
    List<Report> findReportsByTarget(@Param("targetType") Integer targetType, @Param("targetId") Long targetId);
    
    /**
     * 统计指定目标的举报次数
     */
    @Query("SELECT COUNT(r) FROM Report r WHERE r.targetType = :targetType AND r.targetId = :targetId")
    long countReportsByTarget(@Param("targetType") Integer targetType, @Param("targetId") Long targetId);
    
    /**
     * 查找待处理的举报记录
     */
    @Query("SELECT r FROM Report r WHERE r.status = 0 ORDER BY r.createTime ASC")
    Page<Report> findPendingReports(Pageable pageable);
    
    /**
     * 统计待处理的举报数量
     */
    @Query("SELECT COUNT(r) FROM Report r WHERE r.status = 0")
    long countPendingReports();
    
    /**
     * 查找最新的举报记录
     */
    @Query("SELECT r FROM Report r ORDER BY r.createTime DESC")
    Page<Report> findRecentReports(Pageable pageable);
    
    /**
     * 根据处理者ID查找举报记录
     */
    Page<Report> findByHandlerId(Long handlerId, Pageable pageable);
    
    /**
     * 查找指定时间段内的举报记录
     */
    @Query("SELECT r FROM Report r WHERE r.createTime BETWEEN :startTime AND :endTime")
    Page<Report> findReportsByCreateTimeBetween(@Param("startTime") LocalDateTime startTime, 
                                               @Param("endTime") LocalDateTime endTime, 
                                               Pageable pageable);
    
    /**
     * 统计今日举报数量
     */
    @Query("SELECT COUNT(r) FROM Report r WHERE r.createTime >= :startOfDay")
    long countTodayReports(@Param("startOfDay") LocalDateTime startOfDay);
    
    /**
     * 按举报原因类型统计
     */
    @Query("SELECT r.reasonType, COUNT(r) FROM Report r GROUP BY r.reasonType")
    List<Object[]> countByReasonType();
    
    /**
     * 按目标类型统计
     */
    @Query("SELECT r.targetType, COUNT(r) FROM Report r GROUP BY r.targetType")
    List<Object[]> countByTargetType();
    
    /**
     * 按状态统计
     */
    @Query("SELECT r.status, COUNT(r) FROM Report r GROUP BY r.status")
    List<Object[]> countByStatus();
    
    /**
     * 查找被举报最多的目标
     */
    @Query("SELECT r.targetType, r.targetId, COUNT(r) as reportCount FROM Report r GROUP BY r.targetType, r.targetId ORDER BY reportCount DESC")
    Page<Object[]> findMostReportedTargets(Pageable pageable);
    
    /**
     * 查找活跃举报者
     */
    @Query("SELECT r.reporterId, COUNT(r) as reportCount FROM Report r GROUP BY r.reporterId ORDER BY reportCount DESC")
    Page<Object[]> findActiveReporters(Pageable pageable);
    
    /**
     * 复合查询：按状态和目标类型查找
     */
    Page<Report> findByStatusAndTargetType(Integer status, Integer targetType, Pageable pageable);
    
    /**
     * 复合查询：按状态和举报原因类型查找
     */
    Page<Report> findByStatusAndReasonType(Integer status, Integer reasonType, Pageable pageable);
    
    /**
     * 查找用户最近的举报记录
     */
    @Query("SELECT r FROM Report r WHERE r.reporterId = :reporterId ORDER BY r.createTime DESC")
    Page<Report> findRecentReportsByUser(@Param("reporterId") Long reporterId, Pageable pageable);
    
    /**
     * 查找需要管理员关注的高风险举报
     */
    @Query("SELECT r FROM Report r WHERE r.status = 0 AND (r.reasonType IN (2, 3) OR (SELECT COUNT(r2) FROM Report r2 WHERE r2.targetType = r.targetType AND r2.targetId = r.targetId) >= 3)")
    Page<Report> findHighRiskReports(Pageable pageable);
} 